﻿using System;
using System.Data;
using System.Configuration; 
using System.Data.SqlClient;
using HelperUnity;

namespace IYASAKANet_CDDY
{
    /// <summary>
    ///完成SQL数据库的各种操作
    /// </summary>
    public class SqlOperator
    {
        public SqlOperator()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns>返回当前数据库的连接</returns>
        public static SqlConnection createCon()
        {
             
            string strPath = NetCommon.GetDllPath() + "//DllConfig.ini";
            IniFileOperation iniFile = new IniFileOperation(strPath);

            string strdbPath = iniFile.IniReadValue("Setup", "DBPath");
            SqlConnection con = new SqlConnection(strdbPath);
            return con;
        }

        ///配置文件中的连接代码如下：
        /*
         *<connectionStrings>
         *<add name="constring" connectionString="Data Source=.;Initial Catalog=dyjjins;uid=sa;pwd=123456;"/>
         *</connectionStrings>
         */

        /// <summary>
        /// 完成请求所需要的数据集
        /// </summary>
        /// <param name="sql">标准的SQL语句</param>
        /// <returns>返回请求的数据集</returns>
        public static DataSet getSet(string sql)
        {
            SqlConnection con = createCon();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

        }

        /// <summary>
        /// 请求DataReader类型的数据
        /// </summary>
        /// <param name="sql">标准的SQL语句</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader getReader(string sql)
        {
            SqlConnection con = createCon();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行完毕后自动关闭
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
        }
        /// <summary>
        /// 执行删除，修改，插入操作
        /// </summary>
        /// <param name="sql">标准的SQL更新语句</param>
        /// <returns>返回所影响的行数(如果大于零，表示执行成功，否则失败)</returns>
        public static int ExecuteNonQuery(string sql)
        {
            int n;
            SqlConnection con = createCon();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand(sql, con);
                n = cmd.ExecuteNonQuery();
                return n;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 获取数据表中的首行首列(即单值)
        /// </summary>
        /// <param name="sql">标准的SQL语句</param>
        /// <returns>返回单值</returns>
        public static object ExecuteScalar(string sql)
        {
            object obj;
            SqlConnection con = createCon();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand(sql, con);
                obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

        }
        /// <summary>
        /// 存储过程(或SQL语句)返回数据集
        /// </summary>
        /// <param name="con">连接对象</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="cmdText">SQL语句或则存储过程的名字</param>
        /// <param name="commandParameters">参数集合</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteSet(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, con, cmdtype, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                cmd.Parameters.Clear();//清除参数列表

                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 通过存储过程(或SQL语句)执行 删除，修改，添加操作
        /// </summary>
        /// <param name="con">连接对象</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="cmdText">存储过程名，或则SQL语句</param>
        /// <param name="commandParameters">需要的参数集合</param>
        /// <returns>执行结果所影响的行数，如果返回值大于零，表示执行成功，否则失败</returns>
        public static int ExecuteNonQuery(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] commandParameters)
        {
            int count;
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, con, cmdtype, cmdText, commandParameters);
                count = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();//清除参数列表
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 通过存储过程(或SQL语句)获取数据表中的首行首列
        /// </summary>
        /// <param name="con">连接对象</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="cmdText">命令文本，或则存储过程</param>
        /// <param name="commandParameters">参数集合</param>
        /// <returns>返回数据集的首行首列。即单值</returns>
        public static object ExecuteScalar(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] commandParameters)
        {
            object obj;
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, con, cmdtype, cmdText, commandParameters);
                obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {

                    con.Close();

                }
            }

        }
        /// <summary>
        /// 通过存储过程或SQL语句获取SqlDataReader类型的数据
        /// </summary>
        /// <param name="con">连接对象</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="commandParameters">所需要的参数集合</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] commandParameters)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, con, cmdtype, cmdText, commandParameters);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
        }
        /// <summary>
        /// 事务操作
        /// </summary>
        /// <param name="con">连接对象</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="commandParm">命令所需要的参数集合</param>
        /// <returns>执行是否成功。如果为真，则成功，否则失败</returns>
        public static bool transOperator(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] commandParm)
        {
            bool flag = false;
            SqlTransaction myTran = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, con, cmdtype, cmdText, commandParm);
                myTran = con.BeginTransaction();
                cmd.Transaction = myTran;
                cmd.ExecuteNonQuery();

                myTran.Commit();
                flag = true;
                return flag;
            }
            catch (Exception ex)
            {
                myTran.Rollback();
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 为命令的执行做准备，由于方法私有，外界不能调用
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="con">连接对象</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="cmdText">SQL命令或则存储过程的名字</param>
        /// <param name="cmdParms">SQL所需要的参数</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection con, CommandType cmdtype, string cmdText, SqlParameter[] cmdParms)
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            cmd.Connection = con;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdtype;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    if (parm != null)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
            }
        }
    }
}